# -*- coding:utf-8 -*-
"""
    by 张润庶
"""
from testlib.my_Headers import get_headers
from testlib.my_Headers import get_upload_header
from testlib.file_path import CONFIG
from testlib.file_path import UPLOAD_DIR
from typing import Optional, List
from datetime import datetime, date, timedelta
import pandas as pd
import numpy as np
import pg8000.native as pg
import requests
import json
import jsonpath
import jmespath
import time
import re
import yaml
import logging

log = logging.getLogger(__name__)
log.addHandler(logging.NullHandler())


def get_today_begin_time() -> datetime:
    """ 获取今天00:00的时间"""
    return datetime.fromtimestamp(time.mktime(date.today().timetuple()))


def replace_time_param(item: str) -> str:
    """ 替换时间参数 """
    # 时间戳替换
    timestamp_re = r'\${TIMESTAMP(\(\d*\))*}'  # 时间戳的正则表达式
    while True:
        search_timestamp = re.search(timestamp_re, item)
        if search_timestamp:
            timestamp_param_item = search_timestamp.group()  # 需要参数化替换的部分
            if search_timestamp.group(1):
                timestamp = int(datetime.now().timestamp())  # 10位数字的时间戳
            else:
                timestamp = int(datetime.now().timestamp() * 1000)  # 13位数字的时间戳
            item = item.replace(timestamp_param_item, str(timestamp))
        else:
            break

    # 时间替换
    # time_re = r'\${TIME(\(\S+\))?}(\([^\)]*\))?'  # 时间的正则表达式
    time_re = r'\${TIME(\([^\)]*\))?}(\([^\)]*\))?'  # 时间的正则表达式
    while True:
        search_time = re.search(time_re, item)
        if search_time:
            current_time = datetime.now()
            time_param_item = search_time.group()
            if search_time.group(1):  # 时间运算部分
                time_cal = search_time.group(1)
                cal_re = r'\((?P<init>[A-Z]*)(?P<operator>[-|+]?)(?P<num>\d*)(?P<type>[a-z|A-Z]*)\)'  # 时间运算的正则表达式
                result_cal = re.search(cal_re, time_cal)
                if result_cal:
                    cal_dict = result_cal.groupdict()  # 对时间运算部分进行分组(dict)
                    if cal_dict['init']:
                        if cal_dict['init'] == 'TODAY':
                            current_time = get_today_begin_time()
                    if cal_dict['operator'] == '+':
                        if cal_dict['type'] == 'w':
                            current_time = current_time + timedelta(weeks=float(cal_dict['num']))
                        if cal_dict['type'] == 'd':
                            current_time = current_time + timedelta(days=float(cal_dict['num']))
                        if cal_dict['type'] == 'H':
                            current_time = current_time + timedelta(hours=float(cal_dict['num']))
                        if cal_dict['type'] == 'M':
                            current_time = current_time + timedelta(minutes=float(cal_dict['num']))
                        if cal_dict['type'] == 'S':
                            current_time = current_time + timedelta(seconds=float(cal_dict['num']))
                    else:
                        if cal_dict['type'] == 'w':
                            current_time = current_time - timedelta(weeks=float(cal_dict['num']))
                        if cal_dict['type'] == 'd':
                            current_time = current_time - timedelta(days=float(cal_dict['num']))
                        if cal_dict['type'] == 'H':
                            current_time = current_time - timedelta(hours=float(cal_dict['num']))
                        if cal_dict['type'] == 'M':
                            current_time = current_time - timedelta(minutes=float(cal_dict['num']))
                        if cal_dict['type'] == 'S':
                            current_time = current_time - timedelta(seconds=float(cal_dict['num']))
                else:
                    raise Exception("不支持的时间运算,现在的时间运算为：{}".format(time_cal))

            if search_time.group(2):  # 如果存在时间格式化信息
                item = item.replace(time_param_item, current_time.strftime(search_time.group(2)[1:-1]))
            else:
                item = item.replace(time_param_item, current_time.strftime("%Y-%m-%d %H:%M:%S"))
        else:
            break

    return item


def get_excel_data(excel_path: str, sheet_name: str, row_num: int) -> dict:
    """
        将 excel 里指定 Sheet 页的表头和指定行对应的值封装成字典并返回。 字典的key为表头，value为对应行内的值。
        如果表头对应的值为空，则对应的值封装成 ''
    :param excel_path: excel的路径
    :param sheet_name: sheet页的名称
    :param row_num: 行号。在excel里显示行号
    :return: 将表头和行内对应的值封装成字典
    """
    # 通过 pandas 读取指定行的值
    excel_data = pd.read_excel(excel_path, sheet_name).loc[row_num - 2].to_dict()  # type: dict

    # 字典value值的格式转换
    for key, value in excel_data.items():
        if pd.isna(value):  # 将 nan 转换为 ''
            excel_data[key] = ''
        elif isinstance(value, np.int64):  # 将 numpy.int64 转换为 int
            excel_data[key] = value.tolist()
        elif isinstance(value, np.float):  # 将 numpy.float 转换为 float
            excel_data[key] = value
        elif isinstance(value, str):  # 剔除 \n
            excel_data[key] = value.replace('\n', '')
        else:
            excel_data[key] = value

    return excel_data


def compare_json(json1: str, json2: str, exact_matching: bool) -> bool:
    """
        把 json1 和 json2 进行比对, 符合对应的匹配模式返回True, 否则返回False
    :param json1: str格式，可转成json
    :param json2: str格式，可转成json
    :param exact_matching: True: json1 和 json2 的 key-value完全相同
                           False: json1 的 key-value 存在于 json2 中
    """

    json1_dict = json.loads(json1)  # type: dict
    json2_dict = json.loads(json2)  # type: dict

    # 精确匹配
    if exact_matching:
        for key in json1_dict.keys():
            if key in json2_dict.keys():
                if json1_dict[key] != json2_dict[key]:
                    return False
            else:
                return False

        for key in json2_dict.keys():
            if key in json1_dict.keys():
                if json1_dict[key] != json2_dict[key]:
                    return False
            else:
                return False
    else:
        # 部分匹配
        for key in json1_dict.keys():
            if key in json2_dict.keys():
                if json1_dict[key] != json2_dict[key]:
                    return False
            else:
                return False

    return True


def compare_jmes_path(expect_response: str, response: str, exact_matching: bool) -> bool:
    """
        将用 jmes_path 表达式描述的期望结果 和 实际结果 进行对比,符合对应的匹配模式返回True,否则返回False
    :param expect_response: 用 jmes_path 表达式描述的期望结果,str格式,可转成json
    :param response: 实际结果,str格式,可转成json
    :param exact_matching: True: 期望结果与实际结果完全一致
                           False: 期望结果包含在实际结果中
    """
    dict1 = json.loads(expect_response)  # type: dict
    dict2 = json.loads(response)  # type: dict
    dict3 = {}  # jmes_path实际匹配的结果

    # 生成实际用jmespath匹配出来的值
    for key in dict1.keys():
        dict3[key] = jmespath.search(key, dict2)

    # 精确匹配
    if exact_matching:
        for key, value in dict1.items():
            if dict3[key] != value:
                return False
    else:
        # 模糊匹配
        for key, value in dict1.items():
            if str(value) not in str(dict3[key]):
                return False
    return True


def compare_response(excel_data: dict, response: str) -> bool:
    """
        希望结果和实际响应结果进行比对。 符合预期返回 True，反之返回 False
        Excel里的expect_response、response_type全部有值，
        且exact_matching为TRUE/FALSE的时候，才会进行比对，否则直接返回 True
    :param excel_data: Excel 数据
    :param response: 实际接口的响应
    """
    if ExcelCheck.check_response_fields(excel_data):
        # 精确匹配
        if excel_data['exact_matching']:
            # 字符串
            if excel_data['response_type'].lower() == 'str':
                return excel_data['expect_response'] == response
            # json
            if excel_data['response_type'].lower() == 'json':
                return compare_json(excel_data['expect_response'], response, exact_matching=True)
            # 整数
            if excel_data['response_type'].lower() == 'int':
                return excel_data['expect_response'] == int(response)
            # 浮点数
            if excel_data['response_type'].lower() == 'float':
                return excel_data['expect_response'] == float(response)
            # jmes_path表达式
            if excel_data['response_type'].lower() == 'jmes_path':
                return compare_jmes_path(excel_data['expect_response'], response, exact_matching=True)
            # 其他
            raise Exception("当前 response_type 非法, 当前 response_type 为:{}".format(excel_data['response_type'].lower()))

        # 模糊匹配
        else:
            # 字符串
            if excel_data['response_type'].lower() == 'str':
                return excel_data['expect_response'] in response
            # json
            if excel_data['response_type'].lower() == 'json':
                return compare_json(excel_data['expect_response'], response, exact_matching=False)
            # 整数
            if excel_data['response_type'].lower() == 'int':
                return str(excel_data['expect_response']) in response
            # 浮点数
            if excel_data['response_type'].lower() == 'float':
                return str(excel_data['expect_response']) in response
            # jmes_path表达式
            if excel_data['response_type'].lower() == 'jmes_path':
                return compare_jmes_path(excel_data['expect_response'], response, exact_matching=False)
            # 其他
            raise Exception("当前 response_type 非法, 当前 response_type 为:{}".format(excel_data['response_type'].lower()))
    else:
        return True


def compare_expected_response(
        ip: str, excel_path: str, sheet_name: str, row_num: int, replace_items: Optional[dict] = None) -> bool:
    """
        从 excel 获取请求参数，发送请求，获取 response，并与 excel 里的期望结果进行对比
    :param ip: 服务器ip
    :param excel_path: excel的路径
    :param sheet_name: sheet页的名称
    :param row_num: 行号。在excel里显示的行号
    :param replace_items: 参数化替换项
    :return: True:符合预期; False: 不符合预期
    """

    # 从 Excel 里读取数据
    excel_data = get_excel_data(excel_path, sheet_name, row_num)
    logging.info("========>>执行{}的sheet页：{}的第{}行<<========".format(excel_path, sheet_name, row_num))
    logging.info("读取到的excel数据: {}".format(excel_data))

    # 检查 Excel 表头
    ExcelCheck.check_title(excel_data)

    # 检查 必填项
    ExcelCheck.check_required_fields(excel_data)

    # 获取响应结果
    result = request_response(ip, excel_data, replace_items)

    # 比对期望结果和响应结果
    return compare_response(excel_data, result)


def compare_expected_responses(ip: str, excel_path: str, sheet_name: str,
                               row_start_num: Optional[int] = None, row_end_num: Optional[int] = None,
                               replace_items: Optional[dict] = None,
                               rows: Optional[List] = None) -> dict:
    """
        从 excel 获取请求参数，发送请求，获取 response，并与 excel 里的期望结果进行对比.
        符合预期返回 True，不符合预期返回 False.
        支持单行、多行和指定行.
        单行: 入参时不传 row_end_num，读取单行excel数据发送请求并判断响应结果是否符合预期.
        多行: 入参时传入 row_start_num 和 row_end_num，依次读取excel里每行数据发送请求，
               判断响应结果是否符合预期。
        指定行：主要针对需要跳行执行的场景，入参时传入列表形式的行号，程序会依次从列表中读取行号发送请求，
             判断响应结果是否符合预期。
    :param ip: 服务器ip
    :param excel_path: excel的路径
    :param sheet_name: sheet页的名称
    :param row_start_num: 起始行号。在excel里显示的行号
    :param row_end_num: 结束行号。在excel里显示的行号
    :param replace_items: 参数化替换项
    :param rows: 指定某些行
    :return: True:符合预期; False: 不符合预期
    """

    if not replace_items:
        replace_items = {}  # 定义字典存储参数化数据

    # 指定特定的行
    if rows:
        for row in rows:
            replace_items.update(get_extract_items(ip, excel_path, sheet_name, row, replace_items))
        return replace_items

    # 单接口 比对期望结果和响应结果
    if not row_end_num:
        replace_items.update(get_extract_items(ip, excel_path, sheet_name, row_start_num, replace_items))
        return replace_items

    # 多接口 比对期望结果和响应结果
    if row_end_num < row_start_num:
        raise Exception("row_end_num 必须大于 row_start_num, 当前row_start_num:{}, row_end_num:{}"
                        .format(row_start_num, row_end_num))

    # 依次执行Excel的每行请求
    for row_num in range(row_start_num, row_end_num + 1):
        replace_items.update(get_extract_items(ip, excel_path, sheet_name, row_num, replace_items))

    return replace_items


def keyword_from_excel(ip: str, excel_path: str, sheet_name: str,
                       row_start_num: int, row_end_num: int,
                       replace_items: Optional[dict] = None) -> dict:
    """
        从 excel 获取请求参数，发送请求，获取 response，返回提取的数据(字典)
        附加功能： excel 里的期望结果进行对比，如果比对失败，则抛出异常.
        指定excel单行时，row_start_num 和 row_end_num 都需要传入， 传入相等的值即可。
    :param ip: 服务器ip
    :param excel_path: excel的路径
    :param sheet_name: sheet页的名称
    :param row_start_num: 起始行号。在excel里显示的行号
    :param row_end_num: 结束行号。在excel里显示的行号
    :param replace_items: 参数化替换项
    :return: True:符合预期; False: 不符合预期
    """
    # 行号校验
    if row_end_num < row_start_num:
        raise Exception("row_end_num 必须大于 row_start_num, 当前row_start_num:{}, row_end_num:{}"
                        .format(row_start_num, row_end_num))

    # 定义提取项
    if not replace_items:
        replace_items = {}  # 定义字典存储参数化数据

    # 依次执行Excel的每行请求
    for row_num in range(row_start_num, row_end_num + 1):
        try:
            replace_items.update(get_extract_items(ip, excel_path, sheet_name, row_num, replace_items))
        except Exception as e:
            logging.info(e)
            logging.info("{}Sheet页：{}的第{}行执行失败".format(excel_path, sheet_name, row_num))
            raise

    logging.debug("replace_items: {}".format(replace_items))
    return replace_items


def get_extract_items(ip: str, excel_path: str, sheet_name: str, row_num: int, replace_items: dict) -> dict:
    """
        发送请求并从响应结果中提取想要的值
    :param ip: 服务器ip
    :param excel_path: excel的路径
    :param sheet_name: sheet页的名称
    :param row_num: 行号。在excel里显示的行号
    :param replace_items: 当前参数化项
    :return: 提取的值
    """
    # 从 Excel 里读取数据
    excel_data = get_excel_data(excel_path, sheet_name, row_num)
    logging.info("========>>执行{}的sheet页：{}的第{}行<<========".format(excel_path, sheet_name, row_num))
    logging.info("读取到的excel数据: {}".format(excel_data))

    # 检查 Excel 表头
    ExcelCheck.check_title(excel_data)

    # 检查 Excel 必填项
    ExcelCheck.check_required_fields(excel_data)

    # 获取响应结果
    result = request_response(ip, excel_data, replace_items)  # type: str

    # 比对期望结果和响应结果
    if not compare_response(excel_data, result):
        raise Exception("作者:{},{}的Sheet页:{},行号为{}的请求响应结果期望是:{},实际是:{}"
                        .format(excel_data['creator'], excel_path, sheet_name, row_num, excel_data['expect_response'], result))

    # 从结果中提取数据
    if ExcelCheck.check_extract_fields(excel_data):

        # 按 json_key 提取
        if excel_data['extract_model'].lower() == 'json_key':
            return extract_by_json_key(result, excel_data['extract_items'])

        # 按 json_path 提取
        if excel_data['extract_model'].lower() == 'json_path':
            return extract_by_json_path(result, excel_data['extract_items'])

        # 按 jmes_path 提取
        if excel_data['extract_model'].lower() == 'jmes_path':
            return extract_by_jmes_path(result, excel_data['extract_items'])

        # 按 re 提取
        if excel_data['extract_model'].lower() == 're':
            raise Exception("re模式提取暂不支持，等待后续完善")
    else:
        return {}


def extract_by_jmes_path(result: str, extract_items: str) -> dict:
    """
        使用 jmespath 对 result 提取指定项(extract_items)
    """
    # 将 str 转换成 dict
    result_dict = json.loads(result)  # type: dict
    extract_items = json.loads(extract_items)  # type: dict

    for key, value in extract_items.items():
        extract_items[key] = jmespath.search(value, result_dict)
        # 注释掉原因：不对list默认取第1个元素的操作，如果取第N个元素，使用jmespath的管道符进行提取
        # extract_result = jmespath.search(value, result_dict)
        # if isinstance(extract_result, list):
        #     extract_items[key] = extract_result[0]
        # else:
        #     extract_items[key] = extract_result

    logging.debug("extract_items: {}".format(extract_items))
    return extract_items


def extract_by_json_path(result: str, extract_items: str) -> dict:
    """
        使用 jsonpath 对 result 提取指定项(extract_items)
    """
    # 将 str 转换成 dict
    result_dict = json.loads(result)  # type: dict
    extract_items = json.loads(extract_items)  # type: dict

    for key, value in extract_items.items():
        extract_result = jsonpath.jsonpath(result_dict, value)
        if isinstance(extract_result, list):
            extract_items[key] = extract_result[0]
        else:
            raise Exception("使用 jsonpath 提取失败")

    logging.debug("extract_items: {}".format(extract_items))
    return extract_items


def extract_by_json_key(result: str, extract_items: str) -> dict:
    """
        从 result 中提取值。
        将 result 转化成 dict 之后提取指定的 key 对应的 value，只能提取第一层的 key 对应的 value
    """
    # 将 str 转换成 dict
    result_dict = json.loads(result)  # type: dict
    extract_items = json.loads(extract_items)  # type: dict

    for key, value in extract_items.items():
        if value in result_dict:
            extract_items[key] = result_dict[value]
        else:
            raise Exception("响应结果里没有 key：{}".format(value))

    logging.debug("extract_items: {}".format(extract_items))
    return extract_items


def request_response(ip: str, excel_data: dict, replace_items: Optional[dict] = None) -> str:
    """
        根据 excel 里的请求参数获取 response
    :param ip: 服务器ip
    :param excel_data: excel里取出的请求参数
    :param replace_items: 替换项
    :return:
    """
    # 替换参数化的值
    excel_data = replace_param_items(excel_data, replace_items)

    # 拼接 url 中的 ip 信息
    url = "https://{}{}".format(ip, excel_data['request_url'])

    # 定义requests参数列表
    requests_kwargs = {'verify': False}

    # 拼接请求头
    if excel_data['upload_path']:
        requests_kwargs['headers'] = get_upload_header(ip)
        requests_kwargs['files'] = {'file': open(excel_data['upload_path'], 'rb')}
    else:
        requests_kwargs['headers'] = get_headers(ip)

    # 拼接requests参数列表
    if excel_data['request_params']:
        try:
            if excel_data['request_params'].startswith('{'):
                requests_kwargs['params'] = json.loads(excel_data['request_params'])
            else:
                request_params_dict = {}
                params = excel_data['request_params'].split('&')
                for i in params:
                    key, value = i.split('=')
                    request_params_dict[key] = value
                requests_kwargs['params'] = request_params_dict
        except Exception:
            raise Exception("request_params 解析失败，当前 request_params 为: {}".format(excel_data['request_params']))

    if excel_data['request_data']:
        if excel_data['data_type'].lower() == 'json':
            requests_kwargs['json'] = json.loads(excel_data['request_data'])
        elif excel_data['data_type'].lower() == 'form':
            requests_kwargs['data'] = json.loads(excel_data['request_data'])
        else:
            raise Exception("传入的 data_type 不正确,只能传入 json 或 form, 当前传入的是 {}".format(excel_data['data_type']))

    if excel_data['wait_before']:
        logging.info("延迟 {} 秒发送接口请求".format(excel_data['wait_before']))
        time.sleep(excel_data['wait_before'])

    with UEDMResponse(excel_data['request_method'], url=url, **requests_kwargs) as response:
        if response.status_code != excel_data['response_status_code']:
            raise Exception("{}发送请求失败, 当前HTTP状态码为：{}, 期望的HTTP状态码为: {}"
                            .format(url, response.status_code, excel_data['response_status_code']))
        # 获取响应
        logging.debug("response.text: {}".format(response.text))
        return response.text


def replace_param_items(excel_data: dict, replace_items: Optional[dict] = None) -> dict:
    """ 替换掉参数化的内容 """
    # 替换内置参数化项
    if excel_data['upload_path']:
        excel_data['upload_path'] = excel_data['upload_path'].replace("${UPLOAD}", UPLOAD_DIR)

    if excel_data['request_params']:
        excel_data['request_params'] = replace_time_param(excel_data['request_params'])

    if excel_data['request_data']:
        excel_data['request_data'] = replace_time_param(excel_data['request_data'])

    # 替换传入的参数化项
    if replace_items:
        logging.debug("传入的 replace_items: {}".format(replace_items))
        for key, value in replace_items.items():
            excel_data['request_url'] = excel_data['request_url'].replace(key, str(value))

            if excel_data['request_params']:
                if isinstance(value, list):
                    excel_data['request_params'] = excel_data['request_params'].replace(
                        key, json.dumps(value))
                else:
                    excel_data['request_params'] = excel_data['request_params'].replace(key, str(value))

            if excel_data['request_data']:
                if isinstance(value, list):
                    excel_data['request_data'] = excel_data['request_data'].replace(
                        key, json.dumps(value))
                else:
                    excel_data['request_data'] = excel_data['request_data'].replace(key, str(value))
        logging.debug("参数替换之后的excel_data: {}".format(excel_data))

    return excel_data


def extract_test(excel_path: str, sheet_name: str, row_num: int) -> dict:
    """
        提取的测试方法，显示当前的匹配模式和匹配表达式从原始数据提取出来的实际值。
    :param excel_path: excel的路径
    :param sheet_name: sheet页的名称
    :param row_num: 行号
    """
    # 从 Excel 里读取数据
    excel_data = get_excel_data(excel_path, sheet_name, row_num)
    logging.info("========>>执行{}的sheet页：{}的第{}行<<========".format(excel_path, sheet_name, row_num))
    logging.info("读取到的excel数据: {}".format(excel_data))

    if ExcelCheck.check_extract_fields(excel_data):

        # 按 json_key 提取
        if excel_data['extract_model'].lower() == 'json_key':
            return extract_by_json_key(excel_data['raw_data'], excel_data['extract_items'])

        # 按 json_path 提取
        if excel_data['extract_model'].lower() == 'json_path':
            return extract_by_json_path(excel_data['raw_data'], excel_data['extract_items'])

        # 按 jmes_path 提取
        if excel_data['extract_model'].lower() == 'jmes_path':
            return extract_by_jmes_path(excel_data['raw_data'], excel_data['extract_items'])

        # 按 re 提取
        if excel_data['extract_model'].lower() == 're':
            raise Exception("re模式提取暂不支持，等待后续完善")
    else:
        return {}


def get_config() -> dict:
    """ 读取 config.yml 里内容"""
    with open(CONFIG, "r", encoding="utf-8") as f:
        return yaml.load(f, Loader=yaml.FullLoader)


class ExcelCheck:
    """ 对 Excel 的数据进行校验 """

    @staticmethod
    def check_title(excel_data: dict):
        """ 检查 Excel 的表头, 缺少表头则抛出异常 """
        title_list = ['request_method',
                      'request_url',
                      'request_params',
                      'upload_path',
                      'request_data',
                      'data_type',
                      'response_status_code',
                      'expect_response',
                      'response_type',
                      'exact_matching',
                      'extract_items',
                      'extract_model',
                      'creator',
                      'remarks']

        for title in title_list:
            if title not in excel_data:
                raise Exception("Excel里没有表头：{}".format(title))

        logging.debug("Excel 表头检查通过")

    @staticmethod
    def check_required_fields(excel_data: dict):
        """ 检查必填项，有空值抛出异常 """
        required_fields_list = ['request_method', 'request_url',
                                'response_status_code', 'creator']

        for field in required_fields_list:
            if not excel_data[field]:
                raise Exception("{}为必填项，当前为空".format(field))

        logging.debug("Excel 必填项检查通过")

    @staticmethod
    def check_response_fields(excel_data: dict) -> bool:
        """ 检查 response 相关项， 全部有值且exact_matching为TRUE/FALSE的时候返回 True """
        if not excel_data['expect_response']:
            return False
        if not excel_data['response_type']:
            return False
        if excel_data['exact_matching'] == '':
            return False
        if excel_data['exact_matching'] not in [0.0, 1.0]:
            raise Exception("exact_matching 必须为 TRUE 或 FALSE")
        return True

    @staticmethod
    def check_extract_fields(excel_data: dict) -> bool:
        """
            检查 extract_items 和 extract_model, 全部有值
            且 extract_model 在 'json_key', 'json_path', 're' 中取值的时候返回 True
        """
        if not excel_data['extract_items']:
            return False
        if not excel_data['extract_model']:
            return False
        if excel_data['extract_model'] not in ['json_key', 'json_path', 'jmes_path', 're']:
            raise Exception("extract_model 只支持 'json_key', 'json_path', 'jmes_path', 're', 不支持 {}"
                            .format(excel_data['extract_model']))
        return True


class UEDMResponse:
    """
        透传 requests 库的 request 请求，提供每次请求之后自动释放连接的功能，
        并为以后扩展预留代码空间(后续要统计接口响应时间等功能)
    """
    def __init__(self, method: str, url: str, **kwargs):
        self.response = requests.request(method, url, **kwargs)
        logging.debug("url:{}, method:{}, 耗时{}秒"
                      .format(url, method, self.response.elapsed.total_seconds()))
        if get_config()['requests']['response_time_record']:
            pass

    def __enter__(self):
        return self.response

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.response.close()
        logging.debug("response closed")


class PGConnect:
    """ 提供连接postgreSQL数据库的功能 """
    def __init__(self, **kwargs):
        self.conn = pg.Connection(**kwargs)

    def __enter__(self):
        self.start_time = datetime.now()
        return self.conn

    def __exit__(self, exc_type, exc_val, exc_tb):
        if self.conn:
            self.conn.close()

        self.end_time = datetime.now()
        logging.debug("此次执行SQL耗时: {}".format(self.end_time - self.start_time))


def insert_sql(ip: str, excel_path: str, sheet_name: str, row_num: int,
               replace_items: Optional[dict] = None, server_type: str = 'Gpaas'):
    """
        读取 Excel 里的sql语句并插入指定数据库
    :param ip: 服务器ip
    :param excel_path: excel的路径
    :param sheet_name: sheet页的名称
    :param row_num: 行号。在excel里显示的行号
    :param replace_items: 参数化替换项
    :param server_type: UEDM的安装形式，GPass 或 CPass
    """
    # 读取 sql 信息
    excel_data = get_excel_data(excel_path, sheet_name, row_num)

    # 数据库信息
    pg_pool_info = {'host': ip}
    if server_type.lower() == 'gpaas':
        pg_pool_info['database'] = excel_data['静态容器环境数据库名']
        pg_pool_info['port'] = get_config()['postgreSQL']['gpass_port']
        pg_pool_info['user'] = get_config()['postgreSQL']['gpass_user']
        pg_pool_info['password'] = get_config()['postgreSQL']['gpass_password']
    elif server_type.lower() == 'cpaas':
        pg_pool_info['database'] = excel_data['三节点环境数据库名']
        pg_pool_info['port'] = get_config()['postgreSQL']['cpass_port']
        pg_pool_info['user'] = get_config()['postgreSQL']['cpass_user']
        pg_pool_info['password'] = get_config()['postgreSQL']['cpass_password']
    else:
        raise Exception("不支持的 server_type,当前server_type为:{}".format(server_type))

    # 执行 SQL
    with PGConnect(**pg_pool_info) as conn:
        # 替换时间参数
        excel_data['SQL'] = replace_time_param(excel_data['SQL'])

        # 替换传入的参数化项
        if replace_items:
            for key, value in replace_items.items():
                if isinstance(value, list):
                    excel_data['SQL'] = excel_data['SQL'].replace(
                        key, json.dumps(value))
                else:
                    excel_data['SQL'] = excel_data['SQL'].replace(key, str(value))

        conn.run(excel_data['SQL'])


if __name__ == "__main__":
    from file_path import TESTCASES
    logging.basicConfig(level=logging.DEBUG)

    print(compare_expected_responses("127.0.0.1",
                                     TESTCASES + "/A前置条件/A环境初始化/XXXX.xlsx",
                                     "Sheet页名字",
                                     1, 10))


